import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/word.dart';

class DatabaseService {
  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await initDB();
    return _database!;
  }

  Future<Database> initDB() async {
    String path = join(await getDatabasesPath(), 'words_database.db');
    return await openDatabase(
      path,
      version: 2,
      onCreate: (Database db, int version) async {
        await db.execute('''
          CREATE TABLE categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            color TEXT NOT NULL
          )
        ''');

        await db.execute('''
          CREATE TABLE words (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            word TEXT NOT NULL,
            meaning TEXT NOT NULL,
            example TEXT,
            dateAdded TEXT NOT NULL,
            reviewCount INTEGER NOT NULL,
            lastReviewed TEXT NOT NULL,
            categoryId INTEGER,
            FOREIGN KEY (categoryId) REFERENCES categories (id)
          )
        ''');

        await db.execute('''
          CREATE TABLE study_settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dailyGoal INTEGER NOT NULL DEFAULT 10,
            reminderTime TEXT,
            lastStudyDate TEXT
          )
        ''');

        // 插入默认分类
        await db.execute('''
          INSERT INTO categories (name, color) VALUES 
          ('商务', '#FF4CAF50'),
          ('科技', '#FF2196F3'),
          ('生活', '#FFFF9800'),
          ('其他', '#FF9E9E9E')
        ''');

        // 插入默认设置
        await db.execute('''
          INSERT INTO study_settings (dailyGoal, reminderTime) 
          VALUES (10, '20:00')
        ''');

        await _insertSampleData(db);
      },
      onUpgrade: (Database db, int oldVersion, int newVersion) async {
        if (oldVersion < 2) {
          // 添加新表
          await db.execute('''
            CREATE TABLE categories (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name TEXT NOT NULL,
              color TEXT NOT NULL
            )
          ''');

          await db.execute('''
            CREATE TABLE study_settings (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              dailyGoal INTEGER NOT NULL DEFAULT 10,
              reminderTime TEXT,
              lastStudyDate TEXT
            )
          ''');

          // 添加分类列
          await db.execute('ALTER TABLE words ADD COLUMN categoryId INTEGER REFERENCES categories (id)');

          // 插入默认分类
          await db.execute('''
            INSERT INTO categories (name, color) VALUES 
            ('商务', '#FF4CAF50'),
            ('科技', '#FF2196F3'),
            ('生活', '#FFFF9800'),
            ('其他', '#FF9E9E9E')
          ''');

          // 插入默认设置
          await db.execute('''
            INSERT INTO study_settings (dailyGoal, reminderTime) 
            VALUES (10, '20:00')
          ''');
        }
      },
    );
  }

  Future<void> _insertSampleData(Database db) async {
    final sampleWords = [
      {
        'word': 'ephemeral',
        'meaning': '短暂的，瞬息的',
        'example': 'Social media fame can be ephemeral.',
        'dateAdded': DateTime.now().toIso8601String(),
        'reviewCount': 2,
        'lastReviewed': DateTime.now().toIso8601String(),
      },
      {
        'word': 'serendipity',
        'meaning': '意外发现美好事物的能力',
        'example': 'Finding my dream job was pure serendipity.',
        'dateAdded': DateTime.now().toIso8601String(),
        'reviewCount': 3,
        'lastReviewed': DateTime.now().toIso8601String(),
      },
      {
        'word': 'ubiquitous',
        'meaning': '无处不在的，普遍存在的',
        'example': 'Smartphones have become ubiquitous in modern life.',
        'dateAdded': DateTime.now().toIso8601String(),
        'reviewCount': 1,
        'lastReviewed': DateTime.now().toIso8601String(),
      },
      {
        'word': 'eloquent',
        'meaning': '雄辩的，有说服力的',
        'example': 'Her eloquent speech moved the entire audience.',
        'dateAdded': DateTime.now().toIso8601String(),
        'reviewCount': 4,
        'lastReviewed': DateTime.now().toIso8601String(),
      },
      {
        'word': 'resilient',
        'meaning': '有适应力的，能快速恢复的',
        'example': 'Children are often more resilient than adults.',
        'dateAdded': DateTime.now().toIso8601String(),
        'reviewCount': 2,
        'lastReviewed': DateTime.now().toIso8601String(),
      }
    ];

    for (var wordData in sampleWords) {
      await db.insert('words', wordData);
    }
  }

  Future<int> insertWord(Word word) async {
    final db = await database;
    return await db.insert('words', word.toMap());
  }

  Future<List<Word>> getWords() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('words');
    return List.generate(maps.length, (i) => Word.fromMap(maps[i]));
  }

  Future<void> updateWord(Word word) async {
    final db = await database;
    await db.update(
      'words',
      word.toMap(),
      where: 'id = ?',
      whereArgs: [word.id],
    );
  }

  Future<void> deleteWord(int id) async {
    final db = await database;
    await db.delete(
      'words',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<Map<String, dynamic>> getStatistics() async {
    final db = await database;
    final totalWords = Sqflite.firstIntValue(
      await db.rawQuery('SELECT COUNT(*) FROM words')
    ) ?? 0;
    
    final masteredWords = Sqflite.firstIntValue(
      await db.rawQuery('SELECT COUNT(*) FROM words WHERE reviewCount >= 5')
    ) ?? 0;
    
    final needReviewWords = Sqflite.firstIntValue(
      await db.rawQuery('''
        SELECT COUNT(*) FROM words 
        WHERE reviewCount < 5 OR 
        (julianday('now') - julianday(lastReviewed)) > 7
      ''')
    ) ?? 0;

    return {
      'totalWords': totalWords,
      'masteredWords': masteredWords,
      'needReviewWords': needReviewWords,
    };
  }

  // 分类相关方法
  Future<List<Map<String, dynamic>>> getCategories() async {
    final db = await database;
    return await db.query('categories');
  }

  Future<int> updateWordCategory(int wordId, int categoryId) async {
    final db = await database;
    return await db.update(
      'words',
      {'categoryId': categoryId},
      where: 'id = ?',
      whereArgs: [wordId],
    );
  }

  // 学习设置相关方法
  Future<Map<String, dynamic>> getStudySettings() async {
    final db = await database;
    final List<Map<String, dynamic>> results = await db.query('study_settings');
    return results.first;
  }

  Future<void> updateStudySettings({int? dailyGoal, String? reminderTime, required bool smartReminderEnabled}) async {
    final db = await database;
    final Map<String, dynamic> updates = {};
    if (dailyGoal != null) updates['dailyGoal'] = dailyGoal;
    if (reminderTime != null) updates['reminderTime'] = reminderTime;
    
    await db.update('study_settings', updates);
  }

  Future<void> updateLastStudyDate(String date) async {
    final db = await database;
    await db.update('study_settings', {'lastStudyDate': date});
  }

  // 导入导出相关方法
  Future<List<Map<String, dynamic>>> exportWords() async {
    final db = await database;
    return await db.rawQuery('''
      SELECT w.*, c.name as categoryName 
      FROM words w 
      LEFT JOIN categories c ON w.categoryId = c.id
    ''');
  }

  Future<void> importWords(List<Map<String, dynamic>> words) async {
    final db = await database;
    await db.transaction((txn) async {
      for (var word in words) {
        await txn.insert('words', {
          'word': word['word'],
          'meaning': word['meaning'],
          'example': word['example'],
          'dateAdded': DateTime.now().toIso8601String(),
          'reviewCount': word['reviewCount'] ?? 0,
          'lastReviewed': DateTime.now().toIso8601String(),
          'categoryId': word['categoryId'],
        });
      }
    });
  }
}